import datetime
import json
import time

import numpy as np
import onnxruntime
import pymysql


def predict():
    start = time.time()
    sess = onnxruntime.InferenceSession("./data/lstm_model.onnx")

    DB,conDB = connectDB()
    # 查询最新的168*9的数据
    DB.execute("SELECT WateId,Level,Flow FROM md_wate_meas ORDER BY `MeasDT` DESC LIMIT 1512;")
    DB_data = DB.fetchall()
    DB.execute("SELECT MeasDT FROM md_wate_meas ORDER BY `MeasDT` DESC LIMIT 1;")
    date = DB.fetchone()
    if DB_data[len(DB_data) - 1][0] != DB_data[len(DB_data) - 10][0]:
        raise Exception("时间周期错误！")
    input_data = []
    row = []
    flow = []

    # 整理数据至二维
    for index in range(int(len(DB_data))):
        if index % 9 == 0 and index != 0:
            row.extend(flow)
            input_data.append(row)
            row = []
            flow = []
        row.append(DB_data[index][1])
        flow.append(DB_data[index][2])
        if index == int(len(DB_data) - 1):
            row.extend(flow)
            input_data.append(row)
        # print("range-", index)
    input_data = np.array(input_data, dtype=np.float32)
    # 删除空列
    input_data = np.delete(input_data, [10, 12, 13, 15, 16], axis=1)
    input_data = CleanData(input_data)

    # 从 JSON 文件中读取平均数和方差
    with open("./data/config.json", "r") as f:
        config = json.load(f)
    mean = np.array(config["mean"])
    std = np.array(config["std"])

    input_data = (input_data - mean) / std

    input_names = ["input"]
    output_names = ["output"]
    x_input = np.array(input_data, dtype=np.float32).reshape((1, 168, 13))
    ort_inputs = {input_names[0]: x_input}
    ort_outs = sess.run(output_names, ort_inputs)

    Levelpred = ort_outs[0][:,:,0].reshape(-1)  # 将输出reshape为(72,1)数据
    Flowpred = ort_outs[0][:,:,1].reshape(-1)  # 将输出reshape为(72,1)数据

    Levelpred = Levelpred * std[8] + mean[8]
    Flowpred = Flowpred * std[12] + mean[12]

    try:
        # 开始事务
        conDB.begin()

        # 删除预测表
        sql1 = "DELETE FROM md_wate_predict;"
        DB.execute(sql1)

        # 第一天的数据
        for index in range(24):
            PredDT = date[0] + datetime.timedelta(hours=(index + 1))
            LevelValue = Levelpred[index]
            LevelValue = round(LevelValue, 2)

            FlowValue = Flowpred[index]
            FlowValue = round(FlowValue, 2)

            # 1 为水位
            sql = "INSERT INTO md_wate_predict (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 1, PredDT, LevelValue))

            # 2 为流量
            sql = "INSERT INTO md_wate_predict (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 2, PredDT, FlowValue))

        # 历史表
        PredDT = date[0] + datetime.timedelta(hours=1)
        LevelValue = Levelpred[0]
        LevelValue = round(LevelValue, 2)
        FlowValue = Flowpred[index]
        FlowValue = round(FlowValue, 2)
        # 1 为水位
        sql = "INSERT INTO md_wate_predict_hist (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
        DB.execute(sql, ('11', 1, PredDT, LevelValue))
        # 2 为流量
        sql = "INSERT INTO md_wate_predict_hist (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
        DB.execute(sql, ('11', 2, PredDT, FlowValue))

        # 第2，3天的数据
        for index in range(2):
            PredDT = date[0] + datetime.timedelta(days=(index + 1))
            LevelValue = Levelpred[(index + 1)*24:(index + 2)*24].mean()
            LevelValue = round(LevelValue, 2)

            FlowValue = Flowpred[(index + 1) * 24:(index + 2) * 24].mean()
            FlowValue = round(FlowValue, 2)

            sql = "INSERT INTO md_wate_predict (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 1, PredDT, LevelValue))

            sql = "INSERT INTO md_wate_predict (WateId,Kind,PredDT,Value) VALUES (%s,%s,%s,%s)"
            DB.execute(sql, ('11', 2, PredDT, FlowValue))

        # 提交事务
        conDB.commit()
        print("事务提交成功！")

    except Exception as e:
        # 回滚事务
        conDB.rollback()
        print("事务回滚，原因：", str(e))

    # 关闭连接对象，否则会导致连接泄漏，消耗数据库资源
    DB.close()
    # 关闭光标
    DB.close()

    PredictTime = time.time() - start
    Output = "预测完成，耗时" + str("{:.2f}".format(PredictTime)) + "S"
    print(Output)

    return

def connectDB():

    with open("./data/DBconfig.json", "r") as f:
        config = json.load(f)
    host = config["host"]
    port = config["port"]
    user = config["user"]
    password = config["password"]
    db = config["db"]
    charset = config["charset"]

    # 连接数据库
    connection = pymysql.connect(host=host, # host属性
    port=port,  # 端口，默认为3306
    user = user, # 用户名
    password = password, # 此处填登录数据库的密码
    db = db, # 数据库名
    charset = charset,  # 字符编码
    )
    cur = connection.cursor()

    return cur,connection

# 找出异常数据并替换
def CleanData(data):

    data = data[::-1, ::-1]
    # 循环遍历每列数据
    for i in range(data.shape[1]):
        col = data[:, i]
        mean = col.mean()
        std = col.std()
        # 误差为10倍方差
        threshold = 3 * std

        # 根据均值和标准差计算异常值范围
        upper_bound = mean + threshold
        lower_bound = mean - threshold

        if lower_bound < 0:
            lower_bound = 0.1

        # 找到并打印异常值
        for row in range(data.shape[0]):
            if data[row,i] > upper_bound or data[row,i] < lower_bound:
                while(row != 0 or row != data.shape[0]):
                    if data[row-1,i] < upper_bound or data[row-1,i] > lower_bound:
                        # print(f"Replace  row {row} col{i}")
                        data[row, i] = data[row-1, i]
                        break
                # print(f"row {row} col{i}")

    data = np.concatenate((data[:, 4:], data[:, :4]), axis=1)
    return data

predict()